﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ICeFramework.Common.Configuration;
using ICeFramework.Common.DataLayer.Enums;
using ICeFramework.Common.DataLayer.Interfaces;
using ICeFramework.Common.DataLayer;
using ICeFramework.Common.DataLayer.Exceptions;
using ICeFramework.DataAccess.DataLayer.MsSQL;

namespace ICeFramework.DataAccess.DataLayer
{
    public static class Utils
    {
        public static void ExecuteNonQuery(string command, IDbTransaction transaction)
        {
            bool releaseConnection = false;
            if (transaction == null)
            {
                releaseConnection = true;
                transaction = GetNewTransaction();
            }
            IDbConnection idbConnection = transaction.Connection;
            IDbCommand cmd = idbConnection.CreateCommand();
            try
            {
                cmd.Transaction = transaction;
                cmd.CommandText = command;
                cmd.CommandTimeout = 300;
                cmd.ExecuteNonQuery();
                if (releaseConnection)
                {
                    transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (releaseConnection)
                {
                    transaction.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (releaseConnection)
                {
                    if (idbConnection != null)
                    {
                        if (idbConnection.State != ConnectionState.Closed)
                            idbConnection.Close();
                    }
                    cmd = null;
                    transaction = null;
                    idbConnection = null;
                }
            }

        }

        public static object ExecuteScalar(string command, IDbTransaction transaction)
        {
            object returnVal = null;
            bool releaseConnection = false;
            if (transaction == null)
            {
                releaseConnection = true;
                transaction = GetNewTransaction();
            }
            IDbConnection idbConnection = transaction.Connection;
            IDbCommand cmd = idbConnection.CreateCommand();
            try
            {
                cmd.Transaction = transaction;
                cmd.CommandText = command;
                cmd.CommandTimeout = 300;
                returnVal = cmd.ExecuteScalar();
                if (releaseConnection)
                {
                    transaction.Commit();
                }
            }
            catch
            {
                if (releaseConnection)
                {
                    transaction.Rollback();
                }
            }
            finally
            {
                if (releaseConnection)
                {
                    if (idbConnection != null)
                    {
                        if (idbConnection.State != ConnectionState.Closed)
                            idbConnection.Close();
                    }
                    cmd = null;
                    transaction = null;
                    idbConnection = null;
                }
            }
            return returnVal;
        }

        public static DataSet ExecuteSpDataTable(StoredProcedureDefinition sp, IDbTransaction transaction)
        {
            return ExecuteSpDataTable(sp.StoredProcedureName, sp.Parameters, transaction);
        }

        public static DataSet ExecuteSpDataTable(String storeProcedure, DataParameterCollection parameters, IDbTransaction transaction)
        {
            bool releaseConnection = false;
            if (transaction == null)
            {
                releaseConnection = true;
                transaction = GetNewTransaction();
            }
            DataParameterCollection returnParameterCollection = new DataParameterCollection();
            List<IDbDataParameter> outIDbDataParameters = new List<IDbDataParameter>();
            IDbConnection connection = transaction.Connection;
            SqlCommand command = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();

            command.CommandText = storeProcedure;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = connection.ConnectionTimeout;
            command.Transaction = (SqlTransaction)transaction;
            command.Connection = (SqlConnection)transaction.Connection;
            try
            {
                //adaugam la comanda parametri
                if (parameters != null)
                {
                    foreach (DataParameter parameterDefinition in parameters)
                    {
                        IDbDataParameter parameter = command.CreateParameter();
                        parameter.DbType = parameterDefinition.DbType;
                        parameter.Direction = parameterDefinition.Direction;
                        parameter.ParameterName = parameterDefinition.ParameterName;
                        parameter.SourceColumn = parameterDefinition.SourceColumn;
                        parameter.SourceVersion = parameterDefinition.SourceVersion;
                        parameter.Value = parameterDefinition.Value ?? DBNull.Value;

                        command.Parameters.Add(parameter);
                    }
                }

                da.SelectCommand = command;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (releaseConnection)
                {
                    if (connection != null)
                    {
                        if (connection.State != ConnectionState.Closed)
                            connection.Close();
                    }
                    command = null;
                    transaction = null;
                    connection = null;
                }
            }
            return ds;
        }

        public static SqlDataReader ExecuteSpDataReader(StoredProcedureDefinition sp, IDbConnection pConnection)
        {
            return ExecuteSpDataReader(sp.StoredProcedureName, sp.Parameters, pConnection);
        }

        public static SqlDataReader ExecuteSpDataReader(String storeProcedure, DataParameterCollection parameters, IDbConnection p_Connection)
        {

            DataParameterCollection returnParameterCollection = new DataParameterCollection();
            List<IDbDataParameter> outIDbDataParameters = new List<IDbDataParameter>();
            IDbConnection connection = p_Connection;
            SqlCommand command = new SqlCommand();
            SqlDataReader dataReader;

            command.CommandText = storeProcedure;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = connection.ConnectionTimeout * 1000;
            command.Connection = (SqlConnection)p_Connection;
            try
            {
                //adaugam la comanda parametri
                if (parameters != null)
                {
                    foreach (DataParameter parameterDefinition in parameters)
                    {
                        IDbDataParameter parameter = command.CreateParameter();
                        parameter.DbType = parameterDefinition.DbType;
                        parameter.Direction = parameterDefinition.Direction;
                        parameter.ParameterName = parameterDefinition.ParameterName;
                        parameter.SourceColumn = parameterDefinition.SourceColumn;
                        parameter.SourceVersion = parameterDefinition.SourceVersion;
                        parameter.Value = parameterDefinition.Value ?? DBNull.Value;

                        command.Parameters.Add(parameter);
                    }
                }
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
            return dataReader;
        }


        public static IDbTransaction GetNewTransaction()
        {
            IDataAccessService dataAccessService;
            switch (ConfigurationHelper.GetServerType())
            {
                case ServerType.MsSql:
                    dataAccessService = new MsSQLDataAccessService();
                    break;
                case ServerType.MySql:
                    throw new NotImplementedException();
                case ServerType.NotAvailable:
                default:
                    throw new DataAccessException("ServerType is unknown for the current connection.");
            }
            IDbConnection conn = dataAccessService.Connection;
            conn.Open();
            return conn.BeginTransaction();
        }
    }
}
